using CoreProject.DTO;
using CoreProject.IRepository;
using CoreProject.Model;
using Dapper;
using Microsoft.EntityFrameworkCore;
using System.Linq.Expressions;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace CoreProject.Repository
{
    public class UsersRepository : BaseRepository<User>, IUsersRepository
    {
        private readonly EFDbContext _dbContext;

        public UsersRepository(EFDbContext dbContext) : base(dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task<User> GetUser(Expression<Func<User, bool>> whereLambda)
        {
            return await _dbContext.Set<User>().Where(whereLambda)
            .Include(u => u.Role.Dept).Include(u => u.Img)
            .FirstOrDefaultAsync();
        }

        public async Task<List<User>> GetUsers(Expression<Func<User, bool>> whereLambda)
        {
            return await _dbContext.Set<User>()
             .Include(u => u.Role.Dept).Include(u => u.Img)
            .Where(whereLambda).ToListAsync();
        }

        public async Task<(List<User> list, int count)> GetUsersByPage<type>(int page, int size, bool isAsc,
       Expression<Func<User, type>> orderLambda, Expression<Func<User, bool>> whereLambda)
        {
            var users = _dbContext.Set<User>()
             .Include(u => u.Role.Dept).Include(u => u.Img).
            Where(whereLambda).AsQueryable();


            int count = users.Count();

            var list = await users.OrderBy(orderLambda)
            .Skip((page - 1) * size).Take(size).ToListAsync();

            return (list, count);
        }


        public async Task<List<MonthStatisticsDto>> GetMonthStatistics(int starMonth, int endMonth)
        {
            string sql = @"WITH Months AS (
                          SELECT @star AS MonthNumber
                          UNION ALL
                          SELECT MonthNumber +1
                          FROM Months
                          WHERE MonthNumber < @end  
                      )
                      SELECT 
                          m.MonthNumber
                          ,ISNULL(COUNT(t.userID), 0) AS TotalAmount
                      FROM 
                          Months m
                      LEFT JOIN 
                          ( SELECT * FROM Users 
                            --WHERE birthday >'2008-01-01' AND  birthday < '2025-01-01'
                          ) t  ON MONTH(t.birthday) = m.MonthNumber
                      GROUP BY 
                          m.MonthNumber
                      OPTION (MAXRECURSION 0);";

            var parameters = new DynamicParameters();
            parameters.Add("@star", starMonth);
            parameters.Add("@end", endMonth);

            return await DapperHelper.QueryAsync<MonthStatisticsDto>(sql, parameters);
        }

        public async Task<(List<User> list, int count)> VipList<type>(string name,int page, int size, bool isAsc, Expression<Func<User, type>> orderLambda)
        {
            var users = _dbContext.Set<User>()
       .Include(u => u.Role.Dept).Include(u => u.Img).
      Where(u=>u.RoleId==6).AsQueryable();

            if (!string.IsNullOrWhiteSpace(name))
            {
                users = users.Where(n => n.UserName.Contains(name));
            }

            int count = users.Count();

            var list = await users.OrderBy(orderLambda)
            .Skip((page - 1) * size).Take(size).ToListAsync();

            return (list, count);
        }
    }
}
